package cn.xlbweb.cli.dao;

import cn.xlbweb.cli.pojo.dto.UserInsertDTO;
import cn.xlbweb.cli.pojo.dto.UserUpdateDTO;
import cn.xlbweb.cli.pojo.jdo.UserDO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author: bobi
 * @date: 2020/11/30 下午12:16
 * @description:
 */
public interface UserRepository extends JpaRepository<UserDO, Integer>, JpaSpecificationExecutor<UserDO> {

    /**
     * 用户登录
     *
     * @param username
     * @param password
     * @return
     */
    UserDO findByUsernameAndPassword(String username, String password);

    /**
     * 根据账号查询用户
     *
     * @param username
     * @return
     */
    UserDO findByUsername(String username);

    /**
     * 查询当前登录用户信息
     * 注：如果想直接返回VO则需要写HQL语句，不要使用原生语句
     *
     * @param username
     * @return
     */
    @Query(value = "select a.id, a.username, a.phone, a.mail, b.name roleName from t_user a left join t_role b on a.role_id = b.id where a.username = ?1", nativeQuery = true)
    Map<String, Object> getCurrentUser(String username);

    /**
     * 根据id查询用户
     *
     * @param id
     * @return
     */
    @Query(value = "select a.id, a.username, a.phone, a.mail, a.status, a.create_time createTime, a.update_time updateTime, b.name roleName, c.nickname from (t_user a left join t_role b on a.role_id = b.id) left join t_user_info c on a.user_info_id = c.id where a.id = ?1", nativeQuery = true)
    Map<String, Object> getUserById(Integer id);

    /**
     * 模糊搜索用户
     *
     * @param username
     * @param nickname
     * @param pageable
     * @return
     */
    @Query(value = "select a.id, a.username, a.phone, a.mail, a.status, b.nickname from t_user a left join t_user_info b on a.user_info_id = b.id where 1=1 and if(ifnull(?1, '') != '', a.username like concat('%', ?1, '%'), 1=1) and if(ifnull(?2, '') != '', b.nickname like concat('%', ?2, '%'), 1=1)",
            countQuery = "select count(a.id) from t_user a left join t_user_info b on a.user_info_id = b.id where 1=1 and if(ifnull(?1, '') != '', a.username like concat('%', ?1, '%'), 1=1) and if(ifnull(?2, '') != '', b.nickname like concat('%', ?2, '%'), 1=1)",
            nativeQuery = true)
    Page<Map<String, Object>> listUser(String username, String nickname, Pageable pageable);

    /**
     * 添加用户
     * 默认值：
     * 1) status状态 -> 0 账号未激活
     * 2) create_time创建时间 -> now()
     *
     * @param dto
     * @return
     */
    @Modifying
    @Query(value = "insert t_user(username, password, phone, mail, status, create_time) values(:#{#dto.username}, :#{#dto.password}, :#{#dto.phone}, :#{#dto.mail}, 1, now())", nativeQuery = true)
    int insertUser(UserInsertDTO dto);

    /**
     * 修改用户
     * 默认值：
     * 1) update_time创建时间 -> now()
     *
     * @param dto
     * @return
     */
    @Modifying
    @Query(value = "update t_user set phone = :#{#dto.phone}, mail = :#{#dto.mail}, update_time = now() where id = :#{#dto.id}", nativeQuery = true)
    int updateUser(UserUpdateDTO dto);

    /**
     * 删除用户
     *
     * @param ids
     * @return
     */
    @Modifying
    @Query(value = "delete from t_user where id in (?1)", nativeQuery = true)
    int deleteUser(List<Integer> ids);
}
